ALTER TABLE [dbo].[Sale]  WITH CHECK ADD  CONSTRAINT [FK_Sale_Months] FOREIGN KEY([MonthId])
REFERENCES [dbo].[Months] ([MonthId])
GO
ALTER TABLE [dbo].[Sale] CHECK CONSTRAINT [FK_Sale_Months]
GO

CREATE TABLE [dbo].[TargetSCU](
	[TargetSCUId] [int] IDENTITY(1,1) NOT NULL,
	[YearId] [int] NOT NULL,
	[MonthId] [int] NOT NULL,
	[SCUId] [int] NOT NULL,
	[StorePointId] [int] NOT NULL,
	[SaleChannelId] [int] NOT NULL,
	[GroupingName] [varchar](500) NULL,
	[LastUpdateUserName] [varchar](50) NULL,
	[LastUpdateDateTime] [datetime] NULL,
 CONSTRAINT [PK_TargetSCU] PRIMARY KEY CLUSTERED 
(
	[TargetSCUId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TargetSCU]  WITH CHECK ADD  CONSTRAINT [FK_TargetSCU_Months] FOREIGN KEY([MonthId])
REFERENCES [dbo].[Months] ([MonthId])
GO
ALTER TABLE [dbo].[TargetSCU] CHECK CONSTRAINT [FK_TargetSCU_Months]
GO

ALTER TABLE [dbo].[TargetSCU]  WITH CHECK ADD  CONSTRAINT [FK_TargetSCU_SaleChannel] FOREIGN KEY([SaleChannelId])
REFERENCES [dbo].[SaleChannel] ([SaleChannelId])
GO
ALTER TABLE [dbo].[TargetSCU] CHECK CONSTRAINT [FK_TargetSCU_SaleChannel]
GO

ALTER TABLE [dbo].[TargetSCU]  WITH CHECK ADD  CONSTRAINT [FK_TargetSCU_SCU] FOREIGN KEY([SCUId])
REFERENCES [dbo].[SCU] ([SCUId])
GO
ALTER TABLE [dbo].[TargetSCU] CHECK CONSTRAINT [FK_TargetSCU_SCU]
GO

ALTER TABLE [dbo].[TargetSCU]  WITH CHECK ADD  CONSTRAINT [FK_TargetSCU_StorePoint] FOREIGN KEY([StorePointId])
REFERENCES [dbo].[StorePoint] ([StorePointId])
GO
ALTER TABLE [dbo].[TargetSCU] CHECK CONSTRAINT [FK_TargetSCU_StorePoint]
GO

create procedure spTargetSCUList
(
	@MonthId int,
	@YearId int
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	create table #tmpTargetList
	(
		RowId int identity(1, 1),
		SaleChannelId int, 
		SaleChannelName nvarchar(50),
		StorePointId int, 
		StorePointName nvarchar(50),
		SCUId int, 
		SCUName nvarchar(500), 
		SCUGroup nvarchar(100), 
		SCUSubGroup nvarchar(100),
		TargetSCUId int,
		GroupingName nvarchar(500),
		IsSelected bit default(0)
	)
	
	insert into #tmpTargetList
	(SaleChannelId, SaleChannelName, StorePointId, StorePointName, 
		SCUId, SCUName, SCUGroup, SCUSubGroup)
	select SaleChannel.SaleChannelId, SaleChannel.SaleChannelName,
			StorePoint.StorePointId, StorePoint.StorePointName,
			vwSCU.SCUId, vwSCU.SCUName, vwSCU.SCUGroup, vwSCU.SCUSubGroup
	from (select SaleChannelId, StorePointId
			from AP (nolock) 
			where (SaleChannelId is not null) 
				and (StorePointId is not null)
				and (IsDelete = 0)
			group by SaleChannelId, StorePointId) as vwAP left join SaleChannel (nolock) on vwAP.SaleChannelId = SaleChannel.SaleChannelId
														left join StorePoint (nolock) on vwAP.StorePointId = StorePoint.StorePointId,
		(select SCUId, SCUName, SCUGroup, SCUSubGroup
			from SCU (nolock) 
			where IsDelete = 0) as vwSCU
			
	update #tmpTargetList
	set TargetSCUId = TargetSCU.TargetSCUId, GroupingName = TargetSCU.GroupingName, IsSelected = 1
	from TargetSCU
	where (TargetSCU.MonthId = @MonthId)
		and (TargetSCU.YearId = @YearId)
		and (TargetSCU.StorePointId = #tmpTargetList.StorePointId)
		and (TargetSCU.SaleChannelId = #tmpTargetList.SaleChannelId)
		and (TargetSCU.SCUId = #tmpTargetList.SCUId)
	
	select RowId, SaleChannelId, SaleChannelName,
			StorePointId, StorePointName,
			SCUId, SCUName, SCUGroup, SCUSubGroup,
			TargetSCUId, GroupingName, IsSelected
	from #tmpTargetList
	
	drop table #tmpTargetList
	
END	
go